package com.bd.framework.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.TimerTask;

import com.bd.framework.utils.UtilsTool;
import com.bd.framework.utils.database.ConnectionFactory;
import com.bd.framework.utils.database.SQliteConnection;
/***
 * @author hnxdyl@hotmail.com
 * 定时扫描远程info_goodsprice数据,查询最大updatetime是否大于本地SQLite中对应表最大updatetime
 * 如果大于则更新本地SQLite中此时间间隔中的数据
 * 同时需要扫描info_goods数据,扫描方案如上
 * */
public class LoadDataTask extends TimerTask {
	@Override
	public void run() {
		new LoadDataTask().LoadGoodInfo();
		new LoadDataTask().LoadGoodPriceInfo();
	}
	
	public void LoadGoodInfo(){
		Connection sqliteCon = null,sybaseCon = null;
		Statement sqliteStatement = null,sybaseStatement = null;
		String sqliteGoodUpdateTime = "",sybaseGoodUpdateTime = "";
		boolean updateFlag = false;
		//SELECT max(datetime(updatetime)) as updatetime FROM info_goods  -- sqlite查询最大更新日期
		String goodsInfoSQL = "SELECT max(updatetime) as updatetime FROM info_goods";
		try { 
			/**
			 *先更新info_goods表,查询本地和远程此表最近更新时间,并做出比较,如果远程updatetime > 本地updatetime
			 *则更新时间区间查中的数据,否则反之(被更新的数据可能不存在本地的SQLite中)
			 */
			sqliteCon = (Connection) SQliteConnection.getSQliteConnection().getSQLiteCon();
			sqliteStatement = sqliteCon.createStatement();
			ResultSet sqliteGoodsRs = sqliteStatement.executeQuery(goodsInfoSQL);
			
			sybaseCon = ConnectionFactory.getConnectionBASEDB();
			sybaseStatement = sybaseCon.createStatement();
			ResultSet sybaseGoodsRs = sybaseStatement.executeQuery(goodsInfoSQL);
			//本地表数据可能为空
			while(sqliteGoodsRs.next()){
				sqliteGoodUpdateTime = sqliteGoodsRs.getString("updatetime");
			}
			//
			while(sybaseGoodsRs.next()){
				sybaseGoodUpdateTime = sybaseGoodsRs.getString("updatetime");
			}
			
			System.out.println(sqliteGoodUpdateTime+"远程时间:"+sybaseGoodUpdateTime);
			/***
			 * 比较本地库和远程库数据差异,本地的更新时间只有小于或者等于远程的更新时间
			 * 时间格式：yyyy-MM-dd HH:mm:ss
			 * */
			if("".equals(sqliteGoodUpdateTime)){
				updateFlag = true;
			}else{
				updateFlag = UtilsTool.dateCompare(sqliteGoodUpdateTime,sybaseGoodUpdateTime);
			}
			
			if(updateFlag){
				//重要日志记录位置
				//更新本地的SQLite数据库,更新本地库时需要注意goodCode不存在情况。更新依据goodCode为主键
				//查询sybase数据库此时间间隔数据
				try{
					//sqliteCon.setAutoCommit(false);//本地事物
					//sybaseCon.setAutoCommit(false);//远程事物
					String sybaseSQLGoodsInfo = "SELECT a.* FROM  info_goods a WHERE a.updatetime >'"+sybaseGoodUpdateTime+"'"
								+ "a.updatetime <'" + sybaseGoodUpdateTime+"'";
					System.out.println(sybaseSQLGoodsInfo);
					ResultSet updateRs = sybaseStatement.executeQuery(sybaseSQLGoodsInfo);
					while(updateRs.next()){
						String sqliteQuery = "SELECT a.* FROM info_goods a WHERE a.goodscode = '" + updateRs.getString("goodscode")+"'";
						ResultSet rs = sqliteStatement.executeQuery(sqliteQuery);
						if(!(rs.getString("goodscode") == null || rs.getString("goodscode").equals(null))){
							//执行更新操作
							String upSql = "UPDATE INFO_GOODS set INPRICE = '"+updateRs.getString("DELPRICE")+"' WHERE goodscode ='"+
									updateRs.getString("goodscode")+"'";
						}else{
							//执行插入操作
							/***
							 * GOODSCODE ,OLDGOODSCODE ,HELPCODE ,
								BARCODE ,CLASSCODE ,GOODSNAME ,
		  						GOODSBRAND ,SPEC ,GOODDATE ,
								PIECE ,PIECEUNIT ,UNITAGE ,
								INSTATUS ,SALESTATUS ,FACTORYCODE ,
								MANAGECODE ,MANAGERATE ,DELPRICE ,
								GOODSABC ,INTAX ,OUTTAX ,
								INDUSTRY ,INPRICE ,RETPRICE ,
								DELRIGHT ,WEIGHT ,LENGTH ,
								BREADTH ,HEIGHT ,UNITBULK ,
								PIECEBULK ,WEIGHTFLAG ,PROCFLAG ,
								LIVINGFLAG ,PLUCODE ,
								UPDATETIME ,PRODUCAREA ;
							 * 
							 * */
							String sqlInsert = "INSERT INTO INFO_GOODS VALUES ('"+updateRs.getString("GOODSCODE")+"','"+
							 updateRs.getString("OLDGOODSCODE")+"','"+updateRs.getString("HELPCODE")+"','"+updateRs.getString("BARCODE")+"','"+
							 updateRs.getString("CLASSCODE")+"','"+updateRs.getString("GOODSNAME")+"','"+updateRs.getString("GOODSBRAND")+"','"+
							 updateRs.getString("SPEC")+"','"+updateRs.getString("GOODDATE")+"','"+updateRs.getString("PIECE")+"','"+
							 updateRs.getString("PIECEUNIT")+"','"+updateRs.getString("UNITAGE")+"','"+updateRs.getString("INSTATUS")+"','"+
							 updateRs.getString("SALESTATUS")+"','"+updateRs.getString("FACTORYCODE")+"','"+updateRs.getString("MANAGECODE")+"','"+
							 updateRs.getString("MANAGERATE")+"','"+updateRs.getString("DELPRICE")+"','"+updateRs.getString("GOODSABC")+"','"+
							 updateRs.getString("INTAX")+"','"+updateRs.getString("OUTTAX")+"','"+updateRs.getString("INDUSTRY")+"','"+
							 updateRs.getString("INPRICE")+"','"+updateRs.getString("RETPRICE")+"','"+updateRs.getString("DELRIGHT")+"','"+
							 updateRs.getString("WEIGHT")+"','"+updateRs.getString("LENGTH")+"','"+updateRs.getString("BREADTH")+"','"+
							 updateRs.getString("HEIGHT")+"','"+updateRs.getString("UNITBULK")+"','"+updateRs.getString("PIECEBULK")+"','"+
							 updateRs.getString("WEIGHTFLAG")+"','"+updateRs.getString("PROCFLAG")+"','"+updateRs.getString("LIVINGFLAG")+"','"+
							 updateRs.getString("PLUCODE")+"','"+updateRs.getString("UPDATETIME")+"','"+updateRs.getString("PRODUCAREA")+"')";
							sqliteStatement.execute(sqlInsert);
						}
					}
				}catch (SQLException e){
					sybaseCon.rollback();
					sqliteCon.rollback();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				//关闭本地SQlite连接
				sqliteStatement.close();
				sqliteCon.close();
				//关闭远程sybase连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			sybaseCon.commit();
			sqliteCon.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void LoadGoodPriceInfo(){
		Connection sqliteCon = null,sybaseCon = null;
		Statement sqliteStatement = null,sybaseStatement = null;
		String godPricUpdatTimSQlit = "",godPricUpdatTimSybas = "";
		boolean updateFlag = false;
		//SELECT max(datetime(updatetime)) as updatetime FROM info_goods -- sqlite查询最大更新日期
		String goodsPriceSQL = "SELECT max(updatetime) as updatetime FROM info_goodsprice;";
		try { 
			/**
			 *先更新info_goods表,查询本地和远程此表最近更新时间,并做出比较,如果远程updatetime > 本地updatetime
			 *则更新时间区间查中的数据,否则反之(被更新的数据可能不存在本地的SQLite中)
			 */
			sqliteCon = (Connection) SQliteConnection.getSQliteConnection().getSQLiteCon();
			sqliteStatement = sqliteCon.createStatement();
			sybaseCon = ConnectionFactory.getConnectionBASEDB();
			sybaseStatement = sybaseCon.createStatement();
		
			//info_goodPrice 更新
			ResultSet sqliteGodPricRs = sqliteStatement.executeQuery(goodsPriceSQL);
			ResultSet sybaseGodPricRs = sybaseStatement.executeQuery(goodsPriceSQL);
			
			//本地表数据可能为空
			while(sqliteGodPricRs.next()){
				godPricUpdatTimSQlit = sqliteGodPricRs.getString("updatetime");
			}
			//
			while(sybaseGodPricRs.next()){
				godPricUpdatTimSybas = sybaseGodPricRs.getString("updatetime");
			}
			
			System.out.println(godPricUpdatTimSQlit+"远程时间:"+godPricUpdatTimSybas);
			/**
			 * 比较本地库和远程库数据差异,本地的更新时间只有小于或者等于远程的更新时间
			 * 其中时间格式:yyyy-MM-dd HH:mm:ss
			 * */
			if("".equals(godPricUpdatTimSQlit)){
				updateFlag = true;
			}else{
				updateFlag = UtilsTool.dateCompare(godPricUpdatTimSQlit,godPricUpdatTimSybas);
			}
			if(updateFlag){
				//重要日志记录位置
				//更新本地的SQLite数据库,更新本地库时需要注意goodCode不存在情况。更新依据goodCode为主键
				//查询sybase数据库此时间间隔数据
				try{
					sqliteCon.setAutoCommit(false);//本地事物
					sybaseCon.setAutoCommit(false);//远程事物
					String sybaseSQLGoodsPriceInfo = "SELECT a.* FROM  info_goodsprice a WHERE a.updatetime >'"+godPricUpdatTimSQlit+"'"
							+ "a.updatetime <'" + godPricUpdatTimSybas+"'";
					System.out.println(sybaseSQLGoodsPriceInfo);
					ResultSet updateRs = sybaseStatement.executeQuery(sybaseSQLGoodsPriceInfo);
					while(updateRs.next()){
						String sqliteQuery = "SELECT a.* FROM info_goodsprice a WHERE a.goodscode = '" + updateRs.getString("goodscode")+"'";
						ResultSet rs = sqliteStatement.executeQuery(sqliteQuery);
						if(!(rs.getString("goodscode") == null || rs.getString("goodscode").equals(null))){
							//执行更新操作
							String upSql = "INSERT INFO_GOODSPRICE SET INPRICE = '"+updateRs.getString("INPRICE")+"' WHERE goodscode = '"+
									updateRs.getString("GOODSCODE")+"', AND MARKETCODE ='"+updateRs.getString("MARKETCODE")+"'";
						}else{
							//执行插入操作
							/***
							 * 
							 * GOODSCODE ,COUNTCODE ,MARKETCODE,
								INPRICE ,MANAGERATE ,RETAILPRICE ,
								PRICEDOWN ,PRICEUP ,SELLFLAG ,
								NUMBERFLAG ,DYNAMICFLAG ,PRESENT ,
								MEMBERPRICE ,MEMBERFLAG ,UPDATETIME ,
								POINTFLAG ,RATE ;
							 * */
							String insertSql = "INSERT INTO INFO_GOODSPRICE VALUES ('"+updateRs.getString("GOODSCODE")+"','"+
									updateRs.getString("COUNTCODE")+"','"+updateRs.getString("MARKETCODE")+"','"+updateRs.getString("INPRICE")+"','"+
									updateRs.getString("MANAGERATE")+"','"+updateRs.getString("RETAILPRICE")+"','"+updateRs.getString("PRICEDOWN")+"','"+
									updateRs.getString("PRICEUP")+"','"+updateRs.getString("SELLFLAG")+"','"+updateRs.getString("NUMBERFLAG")+"','"+
									updateRs.getString("DYNAMICFLAG")+"','"+updateRs.getString("PRESENT")+"','"+updateRs.getString("MEMBERPRICE")+"','"+
									updateRs.getString("MEMBERFLAG")+"','"+updateRs.getString("UPDATETIME")+"','"+updateRs.getString("POINTFLAG")+"','"+
									updateRs.getString("MEMBERFLAG")+"'";
							sqliteStatement.execute(insertSql);
						}
					}
				}catch (SQLException e){
					sybaseCon.rollback();
					sqliteCon.rollback();
				}
			}	
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				//关闭本地SQlite连接
				sqliteStatement.close();
				sqliteCon.close();
				//关闭远程sybase连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			sybaseCon.commit();
			sqliteCon.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
